﻿<%@ WebHandler Language="C#" Class="RbAEOSDashBoardV2" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using ReportBusiness;
using System.Net;

/*
 * Description: 热表厂--SQCDP生产绩效看板V2
 * Copyright(c): 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:2023.10.10
 */

public class RbAEOSDashBoardV2 : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getSafeSvgData":
                        r = getSafeSvgData(jObject);//s-svg数据
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getSafeSvgDetail":
                        r = getSafeSvgDetail(jObject);//s-svg明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getSChartData":
                        r = getSChartData(jObject);//s-折线图
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getSafeChartDetail":
                        r = getSafeSvgDetail(jObject);//s-折线图明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;

                    case "getQualitySvgData":
                        r = getQualitySvgData(jObject);//q-svg数据(一次交检合格率+产品返工次数)
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getQualitySvgDetail":
                        r = getQualitySvgDetail(jObject);//q-svg明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getQChartData":
                        r = getQChartData(jObject);//q-折线图(一次交检合格率)
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getQualityChartDetail":
                        r = getQualityChartDetail(jObject);//q-折线图明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;

                    case "getCostSvgData":
                        r = getCostSvgData(jObject);//c-svg数据
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getCostSvgDetail":
                        r = getCostSvgDetail(jObject);//c-svg明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getCChartData":
                        r = getCChartData(jObject);//c-折线图(在制品批次数)
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getCostChartDetail":
                        r = getCostChartDetail(jObject);//c-折线图明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;

                    case "getDeliverSvgData":
                        r = getDeliverSvgData(jObject);//d-svg数据
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getDeliverSvgDetail":
                        r = getDeliverSvgDetail(jObject);//d-svg明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getDChartData":
                        r = getDChartData(jObject);//d-折线图
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getDeliverChartDetail":
                        r = getDeliverChartDetail(jObject);//d-折线图明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;

                    case "getPersonSvgData":
                        r = getPersonSvgData(jObject);//p-svg
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getPersonSvgDetail":
                        r = getPersonSvgDetail(jObject);//p-svg明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getPChartData":
                        r = getPChartData(jObject);//p-折线图
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getPersonChartDetail":
                        r = getPersonChartDetail(jObject);//p-折线图明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;

                    case "getScrollBoardtData":
                        r = getScrollBoardtData(jObject);//查询轮播表数据
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    //查询s-svg数据
    Result getSafeSvgData(JObject jObject)
    {
        string sql = @"
SELECT d.monthday,EXTRACT(DAY FROM d.monthday) day,CASE WHEN COUNT(e.iswarning) > 0 THEN 1 ELSE 0 END iswarning
FROM
(
  SELECT TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + (ROWNUM - 1) monthday 
  FROM dual 
  CONNECT BY ROWNUM <= (LAST_DAY(TO_DATE('{0}', 'yyyy-mm')) - TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + 1)
) d
LEFT JOIN (
  --查 超过临界值 的指标
  SELECT t.createdate,t.threshold,t.itemname,t.determine,COUNT(t.id) qty,CASE WHEN COUNT(t.id) > t.threshold THEN 1 ELSE 0 END iswarning
  FROM
  (
    SELECT t.yearmonth,t.sqcdptype,t.itemname,t.orgtype,t.unit,t.threshold,t.determine,td.id,td.createdate
    FROM rbaeositem t
    INNER JOIN rbaeositemdetail td ON td.rbaeositemid = t.id
    WHERE t.determine = '大于' AND t.status = 1 AND t.yearmonth = '{0}' AND t.orgtype = '{1}' AND t.sqcdptype = 'S' AND {2}
  ) t
  HAVING COUNT(t.id) > t.threshold 
  GROUP BY t.createdate,t.threshold,t.itemname,t.determine
  --查 未达到预期值 的指标
  UNION ALL
  SELECT d.monthday createdate,t.threshold,t.itemname,t.determine,NVL(rd.qty,0) qty,CASE WHEN NVL(rd.qty,0) < t.threshold THEN 1 ELSE 0 END iswarning
  FROM 
  (
    SELECT TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + (ROWNUM - 1) monthday
    FROM dual
    CONNECT BY ROWNUM <= (LAST_DAY(TO_DATE('{0}', 'yyyy-mm')) - TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + 1)
  ) d
  LEFT JOIN rbaeositem t ON t.determine = '小于' AND t.status = 1 AND t.yearmonth = '{0}' AND t.orgtype = '{1}' AND t.sqcdptype = 'S' AND {2}
  LEFT JOIN (
    SELECT rd.createdate,t.itemname,COUNT(rd.id) qty
    FROM rbaeositemdetail rd
    LEFT JOIN rbaeositem t ON rd.rbaeositemid = t.id
    WHERE t.determine = '小于' AND t.status = 1 AND t.yearmonth = '{0}' AND t.orgtype = '{1}' AND t.sqcdptype = 'S' AND {2}
    GROUP BY rd.createdate,t.itemname
  ) rd ON rd.createdate = d.monthday AND rd.itemname = t.itemname
  WHERE NVL(rd.qty,0) < t.threshold
) e ON d.monthday = e.createdate
GROUP BY d.monthday
ORDER BY d.monthday";

        string yearmonth = jObject["yearmonth"].ToString();
        JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
        string query = "", orgtype = "";
        if (selectOrg.Count == 3)
        {
            orgtype = "班组级";
            query = string.Format("t.teamid = '{0}'", selectOrg[2].ToString());
        }
        else if (selectOrg.Count == 2)
        {
            orgtype = "工区级";
            query = string.Format("t.workcenterid = '{0}'", selectOrg[1].ToString());
        }
        else
        {
            orgtype = "分厂级";
            query = string.Format("t.factoryid = '{0}'", selectOrg[0].ToString());
        }

        sql = string.Format(sql, yearmonth, orgtype, query);
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //查询q-svg数据
    Result getQualitySvgData(JObject jObject)
    {
        string sql = @"
SELECT d.monthday,EXTRACT(DAY FROM d.monthday) day,CASE WHEN COUNT(e.isover) > 0 THEN 1 ELSE 0 END isover
FROM
(
  SELECT TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + (ROWNUM - 1) monthday 
  FROM dual 
  CONNECT BY ROWNUM <= (LAST_DAY(TO_DATE('{0}', 'yyyy-mm')) - TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + 1)
) d
INNER JOIN rbaeositem t ON t.sqcdptype = 'Q' AND t.status = 1 AND t.yearmonth = '{0}' AND {3}
LEFT JOIN (
  --产品返工次数
  SELECT t.createdate,t.threshold,t.itemname,COUNT(t.id) qty,CASE WHEN COUNT(t.id) > t.threshold THEN 1 ELSE 0 END isover
  FROM
  (
    SELECT t.yearmonth,t.sqcdptype,t.itemname,t.orgtype,t.unit,t.threshold,td.createdate,td.id
    FROM rbaeositem t
    INNER JOIN rbaeositemdetail td ON td.rbaeositemid = t.id
    WHERE t.sqcdptype = 'Q' AND t.status = 1 AND t.yearmonth = '{0}' AND {3}
  ) t
  GROUP BY t.createdate,t.threshold,t.itemname
  HAVING COUNT(t.id) > t.threshold
  
  UNION ALL

  --一次交检验合格率
  SELECT n.createdate,n.threshold,n.itemname,n.qty,n.isover
  FROM 
  (
    SELECT mm.checkdate createdate,mm.threshold,mm.itemname,mm.containerqty qty,
         CASE WHEN mm.determine = '小于' THEN (CASE WHEN mm.passrate < mm.threshold THEN 1 ELSE 0 END)
              WHEN mm.determine = '大于' THEN (CASE WHEN mm.passrate > mm.threshold THEN 1 ELSE 0 END)
         END isover
    FROM 
    (
      SELECT t.checkdate,COUNT(t.containername) containerqty,SUM(DECODE(t.lossqty,0,0,1)) lossqty,
             COUNT(t.containername) - SUM(DECODE(t.lossqty,0,0,1)) passqty,
             ROUND((COUNT(t.containername) - SUM(DECODE(t.lossqty,0,0,1)))/COUNT(t.containername),4) * 100 passrate,
             rt.threshold,rt.unit,'一次交检合格率' itemname,rt.determine
      FROM
      (
        SELECT csd.containername,csd.workflowstepname,csd.workcentername,csd.teamname,
               csd.dispatchqty,csd.dispatchemployeename,csd.dispatchdate,
               csd.reportqty,csd.reportemployeename,csd.reportdate,
               csd.checkemployeename,trunc(csd.checkdate) checkdate,
               csd.passqty,NVL(csd.lossqty,0) lossqty
        FROM apsplandetail aps
        LEFT JOIN containercurrentinfo cci ON cci.containerid = aps.containerid
        LEFT JOIN container c ON c.containerid = cci.containerid
        LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
        LEFT JOIN workcenter w ON w.workcenterid = aps.workcenterid
        LEFT JOIN team t ON t.teamid = aps.teamid
        LEFT JOIN containersteprecord csd ON csd.containerid = aps.containerid AND csd.workflowstepid = aps.workflowstepid
        LEFT JOIN workflowstep ws ON ws.workflowstepid = aps.workflowstepid
        WHERE c.rbcontainertype IS NOT NULL AND csd.checkdate IS NOT NULL AND ws.sequence = 1
              AND aps.endtime >= to_date('{1} 00:00:00','yyyy/mm/dd hh24:mi:ss') 
              AND aps.endtime <= to_date('{2} 23:59:59','yyyy/mm/dd hh24:mi:ss')
              AND csd.checkdate >= to_date('{1} 00:00:00','yyyy/mm/dd hh24:mi:ss') 
              AND csd.checkdate <= to_date('{2} 23:59:59','yyyy/mm/dd hh24:mi:ss')
              AND f.factoryname = '{6}'
              AND {4}
      ) t
      INNER JOIN rbaeositem rt ON rt.itemname = '一次交检合格率' AND rt.status = 1 AND rt.yearmonth = '{0}' AND rt.sqcdptype = 'Q' AND {5}
      GROUP BY t.checkdate,rt.threshold,rt.unit,rt.determine
    ) mm
   ) n
   WHERE n.isover > 0
) e ON d.monthday = e.createdate
GROUP BY d.monthday
ORDER BY d.monthday";
        string yearmonth = jObject["yearmonth"].ToString();
        string startOfSelectMonth = jObject["startOfSelectMonth"].ToString();
        string endOfSelectMonth = jObject["endOfSelectMonth"].ToString();
        string factoryname = jObject["factoryname"].ToString();
        JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
        string query3 = "", query4 = "", query5 = "";
        if (selectOrg.Count == 3)
        {
            query3 = string.Format("t.teamid = '{0}' AND t.orgtype = '班组级'", selectOrg[2].ToString());
            query4 = string.Format("aps.teamid = '{0}'", selectOrg[2].ToString());
            query5 = string.Format("rt.teamid = '{0}' AND rt.orgtype = '班组级'", selectOrg[2].ToString());
        }
        else if (selectOrg.Count == 2)
        {
            query3 = string.Format("t.workcenterid = '{0}' AND t.orgtype = '工区级'", selectOrg[1].ToString());
            query4 = string.Format("aps.workcenterid = '{0}'", selectOrg[1].ToString());
            query5 = string.Format("rt.workcenterid = '{0}' AND rt.orgtype = '工区级'", selectOrg[1].ToString());
        }
        else
        {
            query3 = string.Format("t.factoryid = '{0}' AND t.orgtype = '分厂级'", selectOrg[0].ToString());
            query4 = string.Format("aps.specfactoryid = '{0}'", selectOrg[0].ToString());
            query5 = string.Format("rt.factoryid = '{0}' AND rt.orgtype = '分厂级'", selectOrg[0].ToString());
        }

        sql = string.Format(sql, yearmonth, startOfSelectMonth, endOfSelectMonth, query3, query4, query5, factoryname);
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //查询p-svg数据
    Result getPersonSvgData(JObject jObject)
    {
        string sql = @"
--只统计到分厂级
SELECT d.monthday,EXTRACT(DAY FROM d.monthday) day,CASE WHEN COUNT(e.iswarning) > 0 THEN 1 ELSE 0 END iswarning
FROM
(
  SELECT TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + (ROWNUM - 1) monthday 
  FROM dual 
  CONNECT BY ROWNUM <= (LAST_DAY(TO_DATE('{0}', 'yyyy-mm')) - TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + 1)
) d
LEFT JOIN 
(
  SELECT t.createdate,t.threshold,t.itemname,COUNT(t.id) qty,CASE WHEN COUNT(t.id) > t.threshold THEN 1 ELSE 0 END iswarning
  FROM
  (
    SELECT t.yearmonth,t.sqcdptype,t.itemname,t.orgtype,t.unit,t.threshold,t.determine,td.createdate,td.schedule,td.id,
           CASE WHEN td.schedule < t.threshold THEN 1 ELSE 0 END iswarning
    FROM rbaeositem t
    INNER JOIN rbaeositemdetail td ON td.rbaeositemid = t.id
    WHERE t.status = 1 AND t.yearmonth = '{0}' AND t.orgtype = '分厂级' AND t.sqcdptype = 'P'
  ) t
  GROUP BY t.createdate,t.threshold,t.itemname
  HAVING SUM(t.iswarning) > 0
) e ON d.monthday = e.createdate
GROUP BY d.monthday
ORDER BY d.monthday";
        sql = string.Format(sql, jObject["yearmonth"].ToString());
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //查询c-svg数据
    Result getCostSvgData(JObject jObject)
    {
        string sql = @"
SELECT d.monthday,EXTRACT(DAY FROM d.monthday) day,CASE WHEN COUNT(e.iswarning) > 0 THEN 1 ELSE 0 END iswarning
FROM
(
  SELECT TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + (ROWNUM - 1) monthday 
  FROM dual 
  CONNECT BY ROWNUM <= (LAST_DAY(TO_DATE('{0}', 'yyyy-mm')) - TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + 1)
) d
LEFT JOIN 
(
  SELECT d.monthday createdate,t.threshold,t.itemname,t.determine,NVL(m.qty,0) qty,
         CASE WHEN NVL(m.qty,0) > t.threshold THEN 1 ELSE 0 END iswarning
  FROM 
  (
    SELECT TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + (ROWNUM - 1) monthday
    FROM dual
    CONNECT BY ROWNUM <= (LAST_DAY(TO_DATE('{0}', 'yyyy-mm')) - TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + 1)
  ) d
  LEFT JOIN rbaeositem t ON t.status = 1 AND t.yearmonth = '{0}' AND t.sqcdptype = 'C' AND {4}
  LEFT JOIN 
  (
    SELECT t.endtime,COUNT(t.containername) qty
    FROM
    (
      SELECT w.workcentername,t.teamname,cci.containername,cci.qty,cci.workflowstepname,TRUNC(aps.endtime) endtime
      FROM apsplandetail aps
      LEFT JOIN containercurrentinfo cci ON cci.containerid = aps.containerid --子卡
      LEFT JOIN container c ON c.containerid = cci.containerid
      LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
      LEFT JOIN workcenter w ON w.workcenterid = aps.workcenterid
      LEFT JOIN team t ON t.teamid = aps.teamid
      LEFT JOIN containersubcontainer sub ON sub.subcontainerid = aps.containerid
      LEFT JOIN containercurrentinfo cci2 ON cci2.containerid = sub.containerid --主卡
      LEFT JOIN factory f2 ON f2.factoryid = cci2.originalfactoryid
      INNER JOIN apsplandetail aps2 ON aps2.containerid = sub.containerid AND aps2.workflowstepid = sub.workflowstepid AND aps2.isuse = 0 AND NVL(aps2.status,0) <> 0
      INNER JOIN containershiprecordstep spd ON spd.containerid = cci2.containerid AND spd.workflowstepid = sub.workflowstepid
      LEFT JOIN containershiprecord csd ON csd.containershiprecordid = spd.containershiprecordid AND csd.isuse = 0
      LEFT JOIN containershiprecive csrv ON csrv.containershiprecordid = csd.containershiprecordid AND csrv.isuse = 0
      LEFT JOIN containersteprecord csr ON csr.containerid = aps.containerid AND csr.workflowstepid = aps.workflowstepid AND csr.usecompleted = 1
      LEFT JOIN workflowstep ws ON ws.workflowstepid = aps.workflowstepid
      WHERE c.rbcontainertype IS NOT NULL AND ws.sequence = 1 AND csd.toacceptdate IS NOT NULL AND csr.checkdate IS NULL AND cci.containerstatus <> 2 and cci.isInStore <> 1
            AND aps.endtime >= to_date('{1} 00:00:00','yyyy/mm/dd hh24:mi:ss') AND aps.endtime <= to_date('{2} 23:59:59','yyyy/mm/dd hh24:mi:ss')
            AND f.factoryname = '{3}' AND {5}
    ) t
    GROUP BY t.endtime 
  ) m ON m.endtime = d.monthday
  WHERE NVL(m.qty,0) > t.threshold
) e ON d.monthday = e.createdate
GROUP BY d.monthday
ORDER BY d.monthday";
        string yearmonth = jObject["yearmonth"].ToString();
        string factoryname = jObject["factoryname"].ToString();
        string startOfSelectMonth = jObject["startOfSelectMonth"].ToString();
        string endOfSelectMonth = jObject["endOfSelectMonth"].ToString();
        JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
        string query4 = "", query5 = "";
        if (selectOrg.Count == 3)
        {
            query4 = string.Format("t.teamid = '{0}' AND t.orgtype = '班组级'", selectOrg[2].ToString());
            query5 = string.Format("aps.teamid = '{0}'", selectOrg[2].ToString());
        }
        else if (selectOrg.Count == 2)
        {
            query4 = string.Format("t.workcenterid = '{0}' AND t.orgtype = '工区级'", selectOrg[1].ToString());
            query5 = string.Format("aps.workcenterid = '{0}'", selectOrg[1].ToString());
        }
        else
        {
            query4 = string.Format("t.factoryid = '{0}' AND t.orgtype = '分厂级'", selectOrg[0].ToString());
            query5 = string.Format("aps.specfactoryid = '{0}'", selectOrg[0].ToString());
        }

        sql = string.Format(sql, yearmonth, startOfSelectMonth, endOfSelectMonth, factoryname, query4, query5);
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //查询d-svg数据
    Result getDeliverSvgData(JObject jObject)
    {
        string sql = @"
SELECT d.monthday,EXTRACT(DAY FROM d.monthday) day,CASE WHEN COUNT(e.iswarning) > 0 THEN 1 ELSE 0 END iswarning
FROM
(
  SELECT TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + (ROWNUM - 1) monthday 
  FROM dual 
  CONNECT BY ROWNUM <= (to_date('{2}','yyyy-mm-dd') - to_date('{1}','yyyy-mm-dd') + 1)
) d
LEFT JOIN 
(
  SELECT d.monthday createdate,t.threshold,t.itemname,t.determine,m.intimefinishrate,
         CASE WHEN m.intimefinishrate IS NOT NULL AND m.intimefinishrate < t.threshold THEN 1 ELSE 0 END iswarning
  FROM 
  (
    SELECT TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + (ROWNUM - 1) monthday
    FROM dual
    CONNECT BY ROWNUM <= (to_date('{2}','yyyy-mm-dd') - to_date('{1}','yyyy-mm-dd') + 1)
  ) d
  LEFT JOIN rbaeositem t ON t.status = 1 AND t.yearmonth = '{0}' AND t.sqcdptype = 'D' AND {5}
  INNER JOIN 
  (
      SELECT t.plannedfinishdate,
             COUNT(t.sworkflowstepname) total,
             SUM(CASE WHEN t.status = '已完成' AND t.delaydays = 0 THEN 1 ELSE 0 END) intimefinishQty,
             ROUND(SUM(CASE WHEN t.status = '已完成' AND t.delaydays = 0 THEN 1 ELSE 0 END) / COUNT(t.sworkflowstepname),4) * 100 intimefinishrate
      FROM ( 
          SELECT DISTINCT TRUNC(aps.endtime) plannedfinishdate,f2.factoryname fromfactoryname,f.factoryname tofactoryname,w.workcentername,t.teamname,cci2.containername pcontainername,
                 sub.workflowstepname pworkflowstepname,
                 cci.containername rbcontainername,aps.workflowstepname sworkflowstepname,cci.qty,cci.productname,cci.productdescription,
                 cci.mfgordername,aps2.starttime pstarttime,aps2.endtime pendtime,aps.starttime,aps.endtime,
                 csd.senddate,csd.toacceptdate,csrv.completedate,csrv.recivedate,csr.checkdate,
                 CASE WHEN csd.toacceptdate IS NULL THEN '待接收'
                      WHEN csd.toacceptdate IS NOT NULL AND csr.checkdate IS NULL THEN '待完成'
                      WHEN csr.checkdate IS NOT NULL THEN '已完成'
                 END status,
                 CASE WHEN csr.checkdate IS NOT NULL AND csr.checkdate > aps.endtime THEN ROUND(csr.checkdate - aps.endtime,2)
                      WHEN csr.checkdate IS NOT NULL AND csr.checkdate <= aps.endtime THEN 0
                      WHEN csr.checkdate IS NULL AND SYSDATE > aps.endtime THEN ROUND(SYSDATE - aps.endtime,2)
                      WHEN csr.checkdate IS NULL AND SYSDATE <= aps.endtime THEN 0
                 END delaydays
          FROM apsplandetail aps
          LEFT JOIN containercurrentinfo cci ON cci.containerid = aps.containerid --子卡
          LEFT JOIN container c ON c.containerid = cci.containerid
          LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
          LEFT JOIN workcenter w ON w.workcenterid = aps.workcenterid
          LEFT JOIN team t ON t.teamid = aps.teamid
          LEFT JOIN containersubcontainer sub ON sub.subcontainerid = aps.containerid
          LEFT JOIN containercurrentinfo cci2 ON cci2.containerid = sub.containerid --主卡
          LEFT JOIN factory f2 ON f2.factoryid = cci2.originalfactoryid
          INNER JOIN apsplandetail aps2 ON aps2.containerid = sub.containerid AND aps2.workflowstepid = sub.workflowstepid AND aps2.isuse = 0 AND NVL(aps2.status,0) <> 0
          INNER JOIN containershiprecordstep spd ON spd.containerid = cci2.containerid AND spd.workflowstepid = sub.workflowstepid
          LEFT JOIN containershiprecord csd ON csd.containershiprecordid = spd.containershiprecordid AND csd.isuse = 0
          LEFT JOIN containershiprecive csrv ON csrv.containershiprecordid = csd.containershiprecordid AND csrv.isuse = 0
          LEFT JOIN containersteprecord csr ON csr.containerid = aps.containerid AND csr.workflowstepid = aps.workflowstepid AND csr.usecompleted = 1
          LEFT JOIN workflowstep ws ON ws.workflowstepid = aps.workflowstepid
          WHERE c.rbcontainertype IS NOT NULL AND csd.senddate IS NOT NULL AND cci.containerstatus <> 2 AND ws.sequence = 1
                --AND csd.senddate <= aps2.starttime
                AND aps.endtime >= to_date('{1} 00:00:00','yyyy-MM-dd hh24:mi:ss') 
                AND aps.endtime <= to_date('{3} 23:59:59','yyyy-MM-dd hh24:mi:ss') 
                AND f.factoryname = '{4}' AND {6}
      ) t
      GROUP BY t.plannedfinishdate
  ) m ON m.plannedfinishdate = d.monthday
  WHERE m.intimefinishrate < t.threshold
) e ON d.monthday = e.createdate
GROUP BY d.monthday
ORDER BY d.monthday";
        string yearmonth = jObject["yearmonth"].ToString();
        string startOfDate = jObject["startOfSelectMonth"].ToString();
        string endOfSelectMonth = jObject["endOfSelectMonth"].ToString();
        string factoryname = jObject["factoryname"].ToString();
        string endOfDate = "";
        if (jObject["yearmonth"].ToString() == jObject["thismonth"].ToString())
        {
            endOfDate = jObject["today"].ToString();
        }
        else
        {
            endOfDate = jObject["endOfSelectMonth"].ToString();
        }

        JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
        string query5 = "", query6 = "";
        if (selectOrg.Count == 3)
        {
            query5 = string.Format("t.teamid = '{0}' AND t.orgtype = '班组级'", selectOrg[2].ToString());
            query6 = string.Format("aps.teamid = '{0}'", selectOrg[2].ToString());
        }
        else if (selectOrg.Count == 2)
        {
            query5 = string.Format("t.workcenterid = '{0}' AND t.orgtype = '工区级'", selectOrg[1].ToString());
            query6 = string.Format("aps.workcenterid = '{0}'", selectOrg[1].ToString());
        }
        else
        {
            query5 = string.Format("t.factoryid = '{0}' AND t.orgtype = '分厂级'", selectOrg[0].ToString());
            query6 = string.Format("aps.specfactoryid = '{0}'", selectOrg[0].ToString());
        }
        sql = string.Format(sql, yearmonth, startOfDate, endOfDate, endOfSelectMonth, factoryname, query5, query6);
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //查询s-chart数据
    Result getSChartData(JObject jObject)
    {
        string sql = @"
SELECT d.monthday createdate,EXTRACT(DAY FROM d.monthday) day,NVL(rd.qty,0) qty
FROM
(
  SELECT TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + (ROWNUM - 1) monthday
  FROM dual
  CONNECT BY ROWNUM <= (LAST_DAY(TO_DATE('{0}', 'yyyy-mm')) - TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + 1)
) d
--INNER JOIN rbaeositem t ON t.status = 1 AND t.yearmonth = '{0}' AND t.orgtype = '{1}' AND t.sqcdptype = 'S'
LEFT JOIN
(
  SELECT rd.createdate,COUNT(rd.id) qty
  FROM rbaeositemdetail rd
  INNER JOIN rbaeositem t ON rd.rbaeositemid = t.id
  WHERE t.status = 1 AND t.yearmonth = '{0}' AND t.orgtype = '{1}' AND t.sqcdptype = 'S' AND {2}
  GROUP BY rd.createdate
) rd ON rd.createdate = d.monthday
ORDER BY d.monthday";
        string yearmonth = jObject["yearmonth"].ToString();
        JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
        string orgtype = "", query = "";
        if (selectOrg.Count == 3)
        {
            orgtype = "班组级";
            query = string.Format("t.teamid = '{0}'", selectOrg[2].ToString());
        }
        else if (selectOrg.Count == 2)
        {
            orgtype = "工区级";
            query = string.Format("t.workcenterid = '{0}'", selectOrg[1].ToString());
        }
        else
        {
            orgtype = "分厂级";
            query = string.Format("t.factoryid = '{0}'", selectOrg[0].ToString());
        }
        sql = string.Format(sql, yearmonth, orgtype, query);
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //q-折线图(一次交检合格率)
    Result getQChartData(JObject jObject)
    {
        string sql = @"
SELECT d.monthday,EXTRACT(DAY FROM d.monthday) DAY,NVL(e.passrate,100) passrate
FROM
(
  SELECT TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + (ROWNUM - 1) monthday 
  FROM dual 
  CONNECT BY ROWNUM <= (to_date('{2}','yyyy-mm-dd') - to_date('{1}','yyyy-mm-dd') + 1)
) d
LEFT JOIN rbaeositem t ON t.status = 1 AND t.yearmonth = '{0}' AND t.sqcdptype = 'Q' AND {4}
LEFT JOIN 
(
    SELECT t.checkdate,ROUND((SUM(CASE WHEN t.lossqty = 0 THEN 1 ELSE 0 END))/COUNT(t.workflowstepname),4) * 100 passrate
    FROM (
        SELECT f.factoryname,w.workcentername,t.teamname,cci.containername,aps.workflowstepname,
               aps.starttime,aps.endtime,TRUNC(csr.checkdate) checkdate,NVL(csr.lossqty,0) lossqty,csr.passqty
        FROM apsplandetail aps
        LEFT JOIN containercurrentinfo cci ON cci.containerid = aps.containerid
        LEFT JOIN container c ON c.containerid = cci.containerid
        LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
        LEFT JOIN workcenter w ON w.workcenterid = aps.workcenterid
        LEFT JOIN team t ON t.teamid = aps.teamid
        LEFT JOIN containersteprecord csr ON csr.containerid = aps.containerid AND csr.workflowstepid = aps.workflowstepid
        LEFT JOIN workflowstep ws ON ws.workflowstepid = aps.workflowstepid
        WHERE c.rbcontainertype IS NOT NULL AND csr.checkdate IS NOT NULL AND ws.sequence = 1
              AND aps.endtime >= to_date('{1} 00:00:00','yyyy/mm/dd hh24:mi:ss') 
              AND aps.endtime <= to_date('{2} 23:59:59','yyyy/mm/dd hh24:mi:ss')
              AND csr.checkdate >= to_date('{1} 00:00:00','yyyy/mm/dd hh24:mi:ss') 
              AND csr.checkdate <= to_date('{2} 23:59:59','yyyy/mm/dd hh24:mi:ss') AND f.factoryname = '{3}'
              AND {5}
              
        ORDER BY cci.containername,aps.workflowstepname
    ) t
    GROUP BY t.checkdate
) e ON d.monthday = e.checkdate
GROUP BY d.monthday,e.passrate
ORDER BY d.monthday";
        string startOfDate = jObject["startOfSelectMonth"].ToString();
        string endOfDate = jObject["endOfSelectMonth"].ToString();
        JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
        string yearmonth = jObject["yearmonth"].ToString();
        string factoryname = jObject["factoryname"].ToString();
        string query4 = "", query5 = "";
        if (selectOrg.Count == 3)
        {
            query4 = string.Format("t.teamid = '{0}' AND t.orgtype = '班组级'", selectOrg[2].ToString());
            //查询所选组
            query5 = string.Format("aps.teamid = '{0}'", selectOrg[2].ToString());
        }
        else if (selectOrg.Count == 2)
        {
            query4 = string.Format("t.workcenterid = '{0}' AND t.orgtype = '工区级'", selectOrg[1].ToString());
            //查询所选工区下各组
            query5 = string.Format("aps.workcenterid = '{0}'", selectOrg[1].ToString());
        }
        else
        {
            query4 = string.Format("t.factoryid = '{0}' AND t.orgtype = '分厂级'", selectOrg[0].ToString());
            //查询分厂下所有组
            query5 = string.Format("aps.specfactoryid = '{0}'", selectOrg[0].ToString());
        }

        sql = string.Format(sql, yearmonth, startOfDate, endOfDate, factoryname, query4, query5);
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //c-折线图
    Result getCChartData(JObject jObject)
    {
        string sql = @"
SELECT d.monthday createdate,EXTRACT(DAY FROM d.monthday) DAY,NVL(rd.qty,0) qty
FROM
(
  SELECT TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + (ROWNUM - 1) monthday
  FROM dual
  CONNECT BY ROWNUM <= (LAST_DAY(TO_DATE('{0}', 'yyyy-mm')) - TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + 1)
) d
LEFT JOIN rbaeositem t ON t.status = 1 AND t.yearmonth = '{0}' AND t.sqcdptype = 'C' AND {3}
LEFT JOIN
(
  SELECT t.planfinishdate,COUNT(t.containername) qty
  FROM
  (
      SELECT f.factoryname,w.workcentername,t.teamname,cci.containername,cci2.containername parantcontainername,aps.workflowstepname,
             TRUNC(aps.endtime) planfinishdate,aps.starttime,aps.endtime,csd.senddate,csd.toacceptdate,csr.checkdate
      FROM apsplandetail aps
      LEFT JOIN containercurrentinfo cci ON cci.containerid = aps.containerid --子卡
      LEFT JOIN container c ON c.containerid = cci.containerid
      LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
      LEFT JOIN workcenter w ON w.workcenterid = aps.workcenterid
      LEFT JOIN team t ON t.teamid = aps.teamid
      LEFT JOIN containersubcontainer sub ON sub.subcontainerid = aps.containerid
      LEFT JOIN containercurrentinfo cci2 ON cci2.containerid = sub.containerid --主卡
      LEFT JOIN factory f2 ON f2.factoryid = cci2.originalfactoryid
      INNER JOIN apsplandetail aps2 ON aps2.containerid = sub.containerid AND aps2.workflowstepid = sub.workflowstepid AND aps2.isuse = 0 AND NVL(aps2.status,0) <> 0
      INNER JOIN containershiprecordstep spd ON spd.containerid = cci2.containerid AND spd.workflowstepid = sub.workflowstepid
      LEFT JOIN containershiprecord csd ON csd.containershiprecordid = spd.containershiprecordid AND csd.isuse = 0
      LEFT JOIN containershiprecive csrv ON csrv.containershiprecordid = csd.containershiprecordid AND csrv.isuse = 0
      LEFT JOIN containersteprecord csr ON csr.containerid = aps.containerid AND csr.workflowstepid = aps.workflowstepid AND csr.usecompleted = 1
      LEFT JOIN workflowstep ws ON ws.workflowstepid = aps.workflowstepid
      WHERE c.rbcontainertype IS NOT NULL AND ws.sequence = 1 AND csd.toacceptdate IS NOT NULL AND csr.checkdate IS NULL AND cci.containerstatus <> 2 and cci.isInStore <> 1
            AND aps.endtime >= to_date('{1} 00:00:00','yyyy/mm/dd hh24:mi:ss') 
            AND aps.endtime <= to_date('{2} 23:59:59','yyyy/mm/dd hh24:mi:ss')
            AND {4}
            AND f.factoryname = '{5}'
  ) t
  GROUP BY t.planfinishdate
) rd ON rd.planfinishdate = d.monthday
ORDER BY d.monthday";
        string yearmonth = jObject["yearmonth"].ToString();
        string startOfSelectMonth = jObject["startOfSelectMonth"].ToString();
        string endOfSelectMonth = jObject["endOfSelectMonth"].ToString();
        string factoryname = jObject["factoryname"].ToString();
        string query3 = "", query4 = "";

        JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
        if (selectOrg.Count == 3)
        {
            query3 = string.Format("t.teamid = '{0}' AND t.orgtype = '班组级'", selectOrg[2].ToString());
            query4 = string.Format("aps.teamid = '{0}'", selectOrg[2].ToString());
        }
        else if (selectOrg.Count == 2)
        {
            query3 = string.Format("t.workcenterid = '{0}' AND t.orgtype = '工区级'", selectOrg[1].ToString());
            query4 = string.Format("aps.workcenterid = '{0}'", selectOrg[1].ToString());
        }
        else
        {
            query3 = string.Format("t.factoryid = '{0}' AND t.orgtype = '分厂级'", selectOrg[0].ToString());
            query4 = string.Format("aps.specfactoryid = '{0}'", selectOrg[0].ToString());
        }

        sql = string.Format(sql, yearmonth, startOfSelectMonth, endOfSelectMonth, query3, query4, factoryname);
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //p-折线图
    Result getPChartData(JObject jObject)
    {
        string sql = @"
--只统计培训计划完成率,且只统计到分厂级
SELECT d.monthday,EXTRACT(DAY FROM d.monthday) DAY,NVL(e.schedule,100) schedule
FROM
(
  SELECT TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + (ROWNUM - 1) monthday 
  FROM dual 
  CONNECT BY ROWNUM <= (LAST_DAY(TO_DATE('{0}', 'yyyy-mm')) - TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + 1)
) d
INNER JOIN 
(
  SELECT t.createdate,MAX(t.schedule) schedule
  FROM
  (
    SELECT td.createdate,td.schedule
    FROM rbaeositem t
    INNER JOIN rbaeositemdetail td ON td.rbaeositemid = t.id
    WHERE t.status = 1 AND t.yearmonth = '{0}' AND t.orgtype = '分厂级' AND t.sqcdptype = 'P' AND t.itemname = '培训计划完成率'
  ) t
  GROUP BY t.createdate
) e ON d.monthday = e.createdate
GROUP BY d.monthday,e.schedule
ORDER BY d.monthday";
        sql = string.Format(sql, jObject["yearmonth"].ToString());
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //D折线图（计划按时完成率）
    Result getDChartData(JObject jObject)
    {
        string sql = @"
SELECT d.monthday,EXTRACT(DAY FROM d.monthday) DAY,NVL(e.intimefinishrate,100) intimefinishrate
FROM
(
  SELECT TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + (ROWNUM - 1) monthday 
  FROM dual 
  CONNECT BY ROWNUM <= (to_date('{2}','yyyy-mm-dd') - to_date('{1}','yyyy-mm-dd') + 1)
) d
LEFT JOIN rbaeositem t ON t.status = 1 AND t.yearmonth = '{0}' AND t.sqcdptype = 'D' AND {5}
LEFT JOIN 
(
    SELECT t.planfinishdate,
           COUNT(t.sworkflowstepname) total,
           SUM(CASE WHEN t.status = '已完成' AND t.delaydays = 0 THEN 1 ELSE 0 END) intimefinishQty,
           ROUND(SUM(CASE WHEN t.status = '已完成' AND t.delaydays = 0 THEN 1 ELSE 0 END) / COUNT(t.sworkflowstepname),4) * 100 intimefinishrate
    FROM ( 
        SELECT DISTINCT TRUNC(aps.endtime) planfinishdate,f2.factoryname fromfactoryname,f.factoryname tofactoryname,w.workcentername,t.teamname,cci2.containername pcontainername,
               sub.workflowstepname pworkflowstepname,
               cci.containername rbcontainername,aps.workflowstepname sworkflowstepname,cci.qty,cci.productname,cci.productdescription,
               cci.mfgordername,aps2.starttime pstarttime,aps2.endtime pendtime,aps.starttime,aps.endtime,
               csd.senddate,csd.toacceptdate,csrv.completedate,csrv.recivedate,csr.checkdate,
               CASE WHEN csd.toacceptdate IS NULL THEN '待接收'
                    WHEN csd.toacceptdate IS NOT NULL AND csr.checkdate IS NULL THEN '待完成'
                    WHEN csr.checkdate IS NOT NULL THEN '已完成'
               END status,
               CASE WHEN csr.checkdate IS NOT NULL AND csr.checkdate > aps.endtime THEN ROUND(csr.checkdate - aps.endtime,2)
                    WHEN csr.checkdate IS NOT NULL AND csr.checkdate <= aps.endtime THEN 0
                    WHEN csr.checkdate IS NULL AND SYSDATE > aps.endtime THEN ROUND(SYSDATE - aps.endtime,2)
                    WHEN csr.checkdate IS NULL AND SYSDATE <= aps.endtime THEN 0
               END delaydays
        FROM apsplandetail aps
        LEFT JOIN containercurrentinfo cci ON cci.containerid = aps.containerid --子卡
        LEFT JOIN container c ON c.containerid = cci.containerid
        LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
        LEFT JOIN workcenter w ON w.workcenterid = aps.workcenterid
        LEFT JOIN team t ON t.teamid = aps.teamid
        LEFT JOIN containersubcontainer sub ON sub.subcontainerid = aps.containerid
        LEFT JOIN containercurrentinfo cci2 ON cci2.containerid = sub.containerid --主卡
        LEFT JOIN factory f2 ON f2.factoryid = cci2.originalfactoryid
        INNER JOIN apsplandetail aps2 ON aps2.containerid = sub.containerid AND aps2.workflowstepid = sub.workflowstepid AND aps2.isuse = 0 AND NVL(aps2.status,0) <> 0
        INNER JOIN containershiprecordstep spd ON spd.containerid = cci2.containerid AND spd.workflowstepid = sub.workflowstepid
        LEFT JOIN containershiprecord csd ON csd.containershiprecordid = spd.containershiprecordid AND csd.isuse = 0
        LEFT JOIN containershiprecive csrv ON csrv.containershiprecordid = csd.containershiprecordid AND csrv.isuse = 0
        LEFT JOIN containersteprecord csr ON csr.containerid = aps.containerid AND csr.workflowstepid = aps.workflowstepid AND csr.usecompleted = 1
        LEFT JOIN workflowstep ws ON ws.workflowstepid = aps.workflowstepid
        WHERE c.rbcontainertype IS NOT NULL AND csd.senddate IS NOT NULL AND cci.containerstatus <> 2 AND ws.sequence = 1
              --AND csd.senddate <= aps2.starttime
              AND aps.endtime >= to_date('{1} 00:00:00','yyyy-MM-dd hh24:mi:ss') 
              AND aps.endtime <= to_date('{2} 23:59:59','yyyy-MM-dd hh24:mi:ss') 
              AND f.factoryname = '{3}' AND {4} 
    ) t
    GROUP BY t.planfinishdate
) e ON d.monthday = e.planfinishdate
GROUP BY d.monthday,e.intimefinishrate
ORDER BY d.monthday";
        string yearmonth = jObject["yearmonth"].ToString();
        string startOfSelectMonth = jObject["startOfSelectMonth"].ToString();
        string endOfSelectMonth = jObject["endOfSelectMonth"].ToString();
        string factoryname = jObject["factoryname"].ToString();
        JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
        string query4 = "", query5 = "";
        if (selectOrg.Count == 3)
        {
            //查询所选组
            query4 = string.Format("aps.teamid = '{0}'", selectOrg[2].ToString());
            query5 = string.Format("t.teamid = '{0}' AND t.orgtype = '班组级'", selectOrg[2].ToString());
        }
        else if (selectOrg.Count == 2)
        {
            //查询所选工区下各组
            query4 = string.Format("aps.workcenterid = '{0}'", selectOrg[1].ToString());
            query5 = string.Format("t.workcenterid = '{0}' AND t.orgtype = '工区级'", selectOrg[1].ToString());
        }
        else
        {
            //查询分厂下所有组
            query4 = string.Format("aps.specfactoryid = '{0}'", selectOrg[0].ToString());
            query5 = string.Format("t.factoryid = '{0}' AND t.orgtype = '分厂级'", selectOrg[0].ToString());
        }

        sql = string.Format(sql, yearmonth, startOfSelectMonth, endOfSelectMonth, factoryname, query4, query5);
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //轮播表数据
    Result getScrollBoardtData(JObject jObject)
    {
        string sql = @"
SELECT t.sqcdptype,t.itemname,td.description,
       to_char(td.createdate,'yyyy/mm/dd') createdate,
       CASE WHEN td.needfinishdate IS NULL THEN '' ELSE to_char(td.needfinishdate,'yyyy/mm/dd') END needfinishdate,
       CASE WHEN td.schedule IS NOT NULL THEN td.schedule || '%' ELSE '' END schedule,
       td.dealemployee
FROM rbaeositem t
INNER JOIN rbaeositemdetail td ON td.rbaeositemid = t.id
WHERE t.sqcdptype <> 'P' AND t.status = 1 AND t.yearmonth = '{0}' AND t.orgtype = '{1}'
UNION ALL
SELECT t.sqcdptype,t.itemname,td.description,
       to_char(td.createdate,'yyyy/mm/dd') createdate,
       CASE WHEN td.needfinishdate IS NULL THEN '' ELSE to_char(td.needfinishdate,'yyyy/mm/dd') END needfinishdate,
       CASE WHEN td.schedule IS NOT NULL THEN td.schedule || '%' ELSE '' END schedule,
       td.dealemployee
FROM rbaeositem t
INNER JOIN rbaeositemdetail td ON td.rbaeositemid = t.id
WHERE t.sqcdptype = 'P' AND t.status = 1 AND t.yearmonth = '{0}' AND t.orgtype = '分厂级'";
        JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
        string orgtype = "";
        if (selectOrg.Count == 3)
        {
            orgtype = "班组级";
        }
        else if (selectOrg.Count == 2)
        {
            orgtype = "工区级";
        }
        else
        {
            orgtype = "分厂级";
        }
        sql = string.Format(sql, jObject["yearmonth"].ToString(), orgtype);
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //s-svg明细
    Result getSafeSvgDetail(JObject jObject)
    {
        string sql = @"
SELECT te.id,te.rbaeositemid,t.yearmonth,t.sqcdptype,t.itemname,t.itemnotes,t.orgtype,t.unit,t.threshold,t.determine,t.status,
       CASE WHEN ta.teamname IS NOT NULL THEN f.factoryname || '/' || w.workcentername || '/' || ta.teamname
            WHEN ta.teamname IS NULL AND w.workcentername IS NOT NULL THEN f.factoryname || '/' || w.workcentername
            ELSE f.factoryname
       END orginfo,
       CASE WHEN t.itemname IN ('一次交检合格率','在制品批次数','计划按时完成率') THEN '1' ELSE '0' END disabled,
       te.description,te.improvemeasures,te.needfinishdate,te.finishdate,te.schedule,te.dealemployee,cci.containername,ws.workflowstepname,
       te.createdate,e.fullname creater,te.lastchangedate,e2.fullname updater,w.workcentername,ta.teamname
FROM rbaeositemdetail te
LEFT JOIN rbaeositem t ON t.id = te.rbaeositemid
LEFT JOIN factory f ON f.factoryid = t.factoryid
LEFT JOIN workcenter w ON w.workcenterid = t.workcenterid
LEFT JOIN team ta ON ta.teamid = t.teamid
LEFT JOIN employee e ON e.employeeid = te.createemployee
LEFT JOIN employee e2 ON e2.employeeid = te.lastchangeemployee
LEFT JOIN containercurrentinfo cci ON cci.containerid = te.containerid
LEFT JOIN workflowstep ws ON ws.workflowstepid = te.workflowstepid
WHERE t.status = 1 AND t.yearmonth = '{0}' AND t.sqcdptype = 'S' AND te.createdate = to_date('{1}','yyyy-mm-dd') AND t.orgtype = '{2}' 
      AND {3}
ORDER BY te.createdate";
        string yearmonth = jObject["yearmonth"].ToString();
        string selectdate = jObject["selectdate"].ToString();
        JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
        string orgtype = "", query = "";
        if (selectOrg.Count == 3)
        {
            orgtype = "班组级";
            query = string.Format("t.teamid = '{0}'", selectOrg[2].ToString());
        }
        else if (selectOrg.Count == 2)
        {
            orgtype = "工区级";
            query = string.Format("t.workcenterid = '{0}'", selectOrg[1].ToString());
        }
        else
        {
            orgtype = "分厂级";
            query = string.Format("t.factoryid = '{0}'", selectOrg[0].ToString());
        }

        sql = string.Format(sql, yearmonth, selectdate, orgtype, query);
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //q-svg明细
    Result getQualitySvgDetail(JObject jObject)
    {
        string sql = @"
--一次交检合格率
SELECT n.* FROM ( SELECT mm.*,
 CASE WHEN mm.determine = '小于' THEN (CASE WHEN mm.passrate < mm.threshold THEN 1 ELSE 0 END)
              WHEN mm.determine = '大于' THEN (CASE WHEN mm.passrate > mm.threshold THEN 1 ELSE 0 END)
         END isover FROM (
SELECT w.workcentername,ta.teamname,ROUND((COUNT(t.containername) - SUM(DECODE(t.lossqty,0,0,1)))/COUNT(t.containername),2) * 100  passrate,
       '' ID,'' rbaeositemid,rt.yearmonth,rt.sqcdptype,rt.itemname,rt.itemnotes,rt.orgtype,rt.unit,rt.threshold,rt.determine,rt.status,
       CASE WHEN ta.teamname IS NOT NULL THEN f.factoryname || '/' || w.workcentername || '/' || ta.teamname
            WHEN ta.teamname IS NULL AND w.workcentername IS NOT NULL THEN f.factoryname || '/' || w.workcentername
            ELSE f.factoryname
       END orginfo,
       NULL DESCRIPTION,NULL improvemeasures,NULL needfinishdate,NULL finishdate,NULL schedule,NULL dealemployee,NULL containername,NULL workflowstepname,
       TRUNC(t.checkdate) createdate,NULL creater,NULL lastchangedate,NULL updater
FROM
(
  SELECT csd.containername,csd.workflowstepname,csd.workcentername,csd.teamname,
         csd.dispatchqty,csd.dispatchemployeename,csd.dispatchdate,
         csd.reportqty,csd.reportemployeename,csd.reportdate,
         csd.checkemployeename,trunc(csd.checkdate) checkdate,
         csd.passqty,NVL(csd.lossqty,0) lossqty
  FROM apsplandetail aps
  LEFT JOIN containercurrentinfo cci ON cci.containerid = aps.containerid
  LEFT JOIN container c ON c.containerid = cci.containerid
  LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
  LEFT JOIN workcenter w ON w.workcenterid = aps.workcenterid
  LEFT JOIN team t ON t.teamid = aps.teamid
  LEFT JOIN containersteprecord csd ON csd.containerid = aps.containerid AND csd.workflowstepid = aps.workflowstepid
  LEFT JOIN workflowstep ws ON ws.workflowstepid = aps.workflowstepid
  WHERE c.rbcontainertype IS NOT NULL AND csd.checkdate IS NOT NULL AND ws.sequence = 1
        AND aps.endtime >= to_date('{1} 00:00:00','yyyy/mm/dd hh24:mi:ss') AND aps.endtime <= to_date('{2} 23:59:59','yyyy/mm/dd hh24:mi:ss')
        AND csd.checkdate >= to_date('{1} 00:00:00','yyyy/mm/dd hh24:mi:ss') AND csd.checkdate <= to_date('{2} 23:59:59','yyyy/mm/dd hh24:mi:ss')
        AND {4} AND f.factoryname = '{7}'
) t
INNER JOIN rbaeositem rt ON rt.itemname = '一次交检合格率' AND rt.status = 1 AND rt.yearmonth = '{0}' AND rt.sqcdptype = 'Q' AND {5}
LEFT JOIN factory f ON f.factoryid = rt.factoryid
LEFT JOIN workcenter w ON w.workcenterid = rt.workcenterid
LEFT JOIN team ta ON ta.teamid = rt.teamid
WHERE TRUNC(t.checkdate) = to_date('{3}','yyyy-mm-dd')
HAVING SUM(DECODE(t.lossqty,0,0,1)) > 0
GROUP BY t.checkdate,rt.yearmonth,rt.sqcdptype,rt.itemname,rt.itemnotes,rt.orgtype,rt.unit,rt.threshold,rt.determine,rt.status,ta.teamname,f.factoryname,w.workcentername
) mm ) n  WHERE n.isover > 0
--产品返工次数
UNION ALL
SELECT NULL workcentername,NULL teamname,COUNT(ID) passrate,NULL id,NULL rbaeositemid,yearmonth,sqcdptype,itemname,NULL itemnotes,orgtype,unit,threshold,determine,status,
orginfo,replace(to_char(wm_concat(DESCRIPTION)),',','---') description,replace(to_char(wm_concat(improvemeasures)),',','---') improvemeasures,NULL needfinishdate,NULL finishdate,NULL schedule,replace(to_char(wm_concat(distinct dealemployee)),',','/') dealemployee,NULL containername, NULL workflowstepname,
createdate,creater,NULL lastchangedate,NULL updater,0 isover FROM (
SELECT w.workcentername,ta.teamname,NULL passrate,te.id,te.rbaeositemid,t.yearmonth,t.sqcdptype,t.itemname,t.itemnotes,t.orgtype,t.unit,t.threshold,t.determine,t.status,
       CASE WHEN ta.teamname IS NOT NULL THEN f.factoryname || '/' || w.workcentername || '/' || ta.teamname
            WHEN ta.teamname IS NULL AND w.workcentername IS NOT NULL THEN f.factoryname || '/' || w.workcentername
            ELSE f.factoryname
       END orginfo,
       te.description,te.improvemeasures,te.needfinishdate,te.finishdate,te.schedule,te.dealemployee,cci.containername,ws.workflowstepname,
        te.createdate,e.fullname creater,te.lastchangedate,e2.fullname updater,0 isover
FROM rbaeositem t
LEFT JOIN  rbaeositemdetail te ON te.rbaeositemid=t.id 
LEFT JOIN factory f ON f.factoryid = t.factoryid
LEFT JOIN workcenter w ON w.workcenterid = t.workcenterid
LEFT JOIN team ta ON ta.teamid = t.teamid
LEFT JOIN employee e ON e.employeeid = te.createemployee
LEFT JOIN employee e2 ON e2.employeeid = te.lastchangeemployee
left JOIN containercurrentinfo cci ON cci.containerid = te.containerid
LEFT JOIN workflowstep ws ON ws.workflowstepid = te.workflowstepid
WHERE t.sqcdptype = 'Q' AND t.status = 1 AND t.yearmonth = '{0}' AND te.createdate = to_date('{3}','yyyy-mm-dd') AND {6}
) GROUP BY yearmonth,sqcdptype,itemname，orgtype,unit,threshold,determine,status,orginfo,createdate,creater
";

        string startOfSelectMonth = jObject["startOfSelectMonth"].ToString();
        string endOfSelectMonth = jObject["endOfSelectMonth"].ToString();
        string yearmonth = jObject["yearmonth"].ToString();
        string factoryname = jObject["factoryname"].ToString();
        string selectdate = jObject["selectdate"].ToString();
        string query4 = "", query5 = "", query6 = "";

        JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
        if (selectOrg.Count == 3)
        {
            query4 = string.Format("aps.teamid = '{0}'", selectOrg[2].ToString());
            query5 = string.Format("rt.teamid = '{0}' AND rt.orgtype = '班组级'", selectOrg[2].ToString());
            query6 = string.Format("t.teamid = '{0}' AND t.orgtype = '班组级'", selectOrg[2].ToString());
        }
        else if (selectOrg.Count == 2)
        {
            query4 = string.Format("aps.workcenterid = '{0}'", selectOrg[1].ToString());
            query5 = string.Format("rt.workcenterid = '{0}' AND rt.orgtype = '工区级'", selectOrg[1].ToString());
            query6 = string.Format("t.workcenterid = '{0}' AND t.orgtype = '工区级'", selectOrg[1].ToString());
        }
        else
        {
            query4 = string.Format("aps.specfactoryid = '{0}'", selectOrg[0].ToString());
            query5 = string.Format("rt.factoryid = '{0}' AND rt.orgtype = '分厂级'", selectOrg[0].ToString());
            query6 = string.Format("t.factoryid = '{0}' AND t.orgtype = '分厂级'", selectOrg[0].ToString());
        }
        sql = string.Format(sql, yearmonth, startOfSelectMonth, endOfSelectMonth, selectdate, query4, query5, query6, factoryname);
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //c-svg明细
    Result getCostSvgDetail(JObject jObject)
    {
        string sql = @"
SELECT w.workcentername,ta.teamname,t.yearmonth,t.sqcdptype,t.threshold,t.itemname,t.unit,t.determine,NVL(m.qty,0) qty,d.monthday createdate
FROM 
(
  SELECT TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + (ROWNUM - 1) monthday
  FROM dual
  CONNECT BY ROWNUM <= (LAST_DAY(TO_DATE('{0}', 'yyyy-mm')) - TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + 1)
) d
LEFT JOIN rbaeositem t ON t.status = 1 AND t.yearmonth = '{0}' AND t.sqcdptype = 'C' AND {3}
LEFT JOIN 
(
  SELECT t.createdate,COUNT(t.containername) qty
  FROM
  (
    SELECT w.workcentername,t.teamname,cci.containername,cci.qty,cci.workflowstepname,TRUNC(aps.endtime) createdate
    FROM apsplandetail aps
    LEFT JOIN containercurrentinfo cci ON cci.containerid = aps.containerid --子卡
    LEFT JOIN container c ON c.containerid = cci.containerid
    LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
    LEFT JOIN workcenter w ON w.workcenterid = aps.workcenterid
    LEFT JOIN team t ON t.teamid = aps.teamid
    LEFT JOIN containersubcontainer sub ON sub.subcontainerid = aps.containerid
    LEFT JOIN containercurrentinfo cci2 ON cci2.containerid = sub.containerid --主卡
    LEFT JOIN factory f2 ON f2.factoryid = cci2.originalfactoryid
    INNER JOIN apsplandetail aps2 ON aps2.containerid = sub.containerid AND aps2.workflowstepid = sub.workflowstepid AND aps2.isuse = 0
    INNER JOIN containershiprecordstep spd ON spd.containerid = cci2.containerid AND spd.workflowstepid = sub.workflowstepid
    LEFT JOIN containershiprecord csd ON csd.containershiprecordid = spd.containershiprecordid AND csd.isuse = 0
    LEFT JOIN containershiprecive csrv ON csrv.containershiprecordid = csd.containershiprecordid AND csrv.isuse = 0
    LEFT JOIN containersteprecord csr ON csr.containerid = aps.containerid AND csr.workflowstepid = aps.workflowstepid AND csr.usecompleted = 1
    LEFT JOIN workflowstep ws ON ws.workflowstepid = aps.workflowstepid
    WHERE c.rbcontainertype IS NOT NULL AND ws.sequence = 1 AND csd.toacceptdate IS NOT NULL AND csr.checkdate IS NULL AND cci.containerstatus <> 2
          AND aps.endtime >= to_date('{1} 00:00:00','yyyy/mm/dd hh24:mi:ss') AND aps.endtime <= to_date('{1} 23:59:59','yyyy/mm/dd hh24:mi:ss')
          AND f.factoryname = '{2}' AND {4}
  ) t
  GROUP BY t.createdate 
) m ON m.createdate = d.monthday
LEFT JOIN workcenter w ON t.workcenterid = w.workcenterid
LEFT JOIN team ta ON ta.teamid = t.teamid
WHERE NVL(m.qty,0) > t.threshold";
        string yearmonth = jObject["yearmonth"].ToString();
        string selectdate = jObject["selectdate"].ToString();
        string factoryname = jObject["factoryname"].ToString();
        string query3 = "", query4 = "";

        JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
        if (selectOrg.Count == 3)
        {
            query3 = string.Format("t.teamid = '{0}' AND t.orgtype = '班组级'", selectOrg[2].ToString());
            query4 = string.Format("aps.teamid = '{0}'", selectOrg[2].ToString());
        }
        else if (selectOrg.Count == 2)
        {
            query3 = string.Format("t.workcenterid = '{0}' AND t.orgtype = '工区级'", selectOrg[1].ToString());
            query4 = string.Format("aps.workcenterid = '{0}'", selectOrg[1].ToString());
        }
        else
        {
            query3 = string.Format("t.factoryid = '{0}' AND t.orgtype = '分厂级'", selectOrg[0].ToString());
            query4 = string.Format("aps.specfactoryid = '{0}'", selectOrg[0].ToString());
        }

        sql = string.Format(sql, yearmonth, selectdate, factoryname, query3, query4);
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //d-svg明细
    Result getDeliverSvgDetail(JObject jObject)
    {
        string sql = @"
SELECT w.workcentername,ta.teamname,d.monthday createdate,t.yearmonth,t.sqcdptype,t.threshold,t.itemname,t.unit,t.determine,m.intimefinishrate
FROM 
(
  SELECT TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + (ROWNUM - 1) monthday
  FROM dual
  CONNECT BY ROWNUM <= (LAST_DAY(TO_DATE('{0}', 'yyyy-mm')) - TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + 1)
) d
LEFT JOIN rbaeositem t ON t.status = 1 AND t.yearmonth = '{0}' AND t.sqcdptype = 'D' AND {3}
LEFT JOIN workcenter w ON w.workcenterid = t.workcenterid
LEFT JOIN team ta ON ta.teamid = t.teamid
INNER JOIN 
(
    SELECT t.planfinishdate,
           COUNT(t.sworkflowstepname) total,
           SUM(CASE WHEN t.status = '已完成' AND t.delaydays = 0 THEN 1 ELSE 0 END) intimefinishQty,
           ROUND(SUM(CASE WHEN t.status = '已完成' AND t.delaydays = 0 THEN 1 ELSE 0 END) / COUNT(t.sworkflowstepname),4) * 100 intimefinishrate
    FROM ( 
        SELECT DISTINCT TRUNC(aps.endtime) planfinishdate,f2.factoryname fromfactoryname,f.factoryname tofactoryname,w.workcentername,t.teamname,cci2.containername pcontainername,
               sub.workflowstepname pworkflowstepname,
               cci.containername rbcontainername,aps.workflowstepname sworkflowstepname,cci.qty,cci.productname,cci.productdescription,
               cci.mfgordername,aps2.starttime pstarttime,aps2.endtime pendtime,aps.starttime,aps.endtime,
               csd.senddate,csd.toacceptdate,csrv.completedate,csrv.recivedate,csr.checkdate,
               CASE WHEN csd.toacceptdate IS NULL THEN '待接收'
                    WHEN csd.toacceptdate IS NOT NULL AND csr.checkdate IS NULL THEN '待完成'
                    WHEN csr.checkdate IS NOT NULL THEN '已完成'
               END status,
               CASE WHEN csr.checkdate IS NOT NULL AND csr.checkdate > aps.endtime THEN ROUND(csr.checkdate - aps.endtime,2)
                    WHEN csr.checkdate IS NOT NULL AND csr.checkdate <= aps.endtime THEN 0
                    WHEN csr.checkdate IS NULL AND SYSDATE > aps.endtime THEN ROUND(SYSDATE - aps.endtime,2)
                    WHEN csr.checkdate IS NULL AND SYSDATE <= aps.endtime THEN 0
               END delaydays
        FROM apsplandetail aps
        LEFT JOIN containercurrentinfo cci ON cci.containerid = aps.containerid --子卡
        LEFT JOIN container c ON c.containerid = cci.containerid
        LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
        LEFT JOIN workcenter w ON w.workcenterid = aps.workcenterid
        LEFT JOIN team t ON t.teamid = aps.teamid
        LEFT JOIN containersubcontainer sub ON sub.subcontainerid = aps.containerid
        LEFT JOIN containercurrentinfo cci2 ON cci2.containerid = sub.containerid --主卡
        LEFT JOIN factory f2 ON f2.factoryid = cci2.originalfactoryid
        INNER JOIN apsplandetail aps2 ON aps2.containerid = sub.containerid AND aps2.workflowstepid = sub.workflowstepid AND aps2.isuse = 0 and nvl(aps2.Status,0) <> 0 --modify by longb 2023-12-08
        INNER JOIN containershiprecordstep spd ON spd.containerid = cci2.containerid AND spd.workflowstepid = sub.workflowstepid
        LEFT JOIN containershiprecord csd ON csd.containershiprecordid = spd.containershiprecordid AND csd.isuse = 0
        LEFT JOIN containershiprecive csrv ON csrv.containershiprecordid = csd.containershiprecordid AND csrv.isuse = 0
        LEFT JOIN containersteprecord csr ON csr.containerid = aps.containerid AND csr.workflowstepid = aps.workflowstepid AND csr.usecompleted = 1
        LEFT JOIN workflowstep ws ON ws.workflowstepid = aps.workflowstepid
        WHERE c.rbcontainertype IS NOT NULL AND csd.senddate IS NOT NULL AND cci.containerstatus <> 2 AND ws.sequence = 1
              --AND csd.senddate <= aps2.starttime
              AND aps.endtime >= to_date('{1} 00:00:00','yyyy-MM-dd hh24:mi:ss') 
              AND aps.endtime <= to_date('{1} 23:59:59','yyyy-MM-dd hh24:mi:ss') 
              AND f.factoryname = '{2}' AND {4} 
    ) t
    GROUP BY t.planfinishdate
) m ON m.planfinishdate = d.monthday
WHERE m.intimefinishrate < t.threshold";
        string yearmonth = jObject["yearmonth"].ToString();
        string selectdate = jObject["selectdate"].ToString();
        string factoryname = jObject["factoryname"].ToString();
        string query3 = "", query4 = "";

        JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
        if (selectOrg.Count == 3)
        {
            query3 = string.Format("t.teamid = '{0}' AND t.orgtype = '班组级'", selectOrg[2].ToString());
            query4 = string.Format("aps.teamid = '{0}'", selectOrg[2].ToString());
        }
        else if (selectOrg.Count == 2)
        {
            query3 = string.Format("t.workcenterid = '{0}' AND t.orgtype = '工区级'", selectOrg[1].ToString());
            query4 = string.Format("aps.workcenterid = '{0}'", selectOrg[1].ToString());
        }
        else
        {
            query3 = string.Format("t.factoryid = '{0}' AND t.orgtype = '分厂级'", selectOrg[0].ToString());
            query4 = string.Format("aps.specfactoryid = '{0}'", selectOrg[0].ToString());
        }
        sql = string.Format(sql, yearmonth, selectdate, factoryname, query3, query4);
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //p-svg明细
    Result getPersonSvgDetail(JObject jObject)
    {
        //只统计分厂级
        string sql = @"
SELECT te.id,te.rbaeositemid,t.yearmonth,t.sqcdptype,t.itemname,t.itemnotes,t.orgtype,t.unit,t.threshold,t.determine,t.status,
       CASE WHEN ta.teamname IS NOT NULL THEN f.factoryname || '/' || w.workcentername || '/' || ta.teamname
            WHEN ta.teamname IS NULL AND w.workcentername IS NOT NULL THEN f.factoryname || '/' || w.workcentername
            ELSE f.factoryname
       END orginfo,
       te.description,te.improvemeasures,te.needfinishdate,te.finishdate,te.schedule,te.dealemployee,cci.containername,ws.workflowstepname,
       te.createdate,e.fullname creater,te.lastchangedate,e2.fullname updater
FROM rbaeositemdetail te
LEFT JOIN rbaeositem t ON t.id = te.rbaeositemid
LEFT JOIN factory f ON f.factoryid = t.factoryid
LEFT JOIN workcenter w ON w.workcenterid = t.workcenterid
LEFT JOIN team ta ON ta.teamid = t.teamid
LEFT JOIN employee e ON e.employeeid = te.createemployee
LEFT JOIN employee e2 ON e2.employeeid = te.lastchangeemployee
LEFT JOIN containercurrentinfo cci ON cci.containerid = te.containerid
LEFT JOIN workflowstep ws ON ws.workflowstepid = te.workflowstepid
WHERE t.status = 1 AND t.yearmonth = '{0}' AND t.orgtype = '分厂级' AND t.sqcdptype = 'P' AND te.createdate = to_date('{1}','yyyy-mm-dd')
      AND te.schedule < t.threshold";
        sql = string.Format(sql, jObject["yearmonth"].ToString(), jObject["selectdate"].ToString());
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //一次交检合格率--Q指标折线图明细
    Result getQualityChartDetail(JObject jObject)
    {
        string sql = @"
SELECT csd.containername,csd.workflowstepname,csd.workcentername,csd.teamname,
       csd.dispatchqty,csd.dispatchemployeename,csd.dispatchdate,
       csd.reportqty,csd.reportemployeename,csd.reportdate,
       csd.checkemployeename,trunc(csd.checkdate) checkdate,
       csd.passqty,
       NVL(csd.lossqty,0) lossqty
FROM apsplandetail aps
LEFT JOIN containercurrentinfo cci ON cci.containerid = aps.containerid
LEFT JOIN container c ON c.containerid = cci.containerid
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
LEFT JOIN workcenter w ON w.workcenterid = aps.workcenterid
LEFT JOIN team t ON t.teamid = aps.teamid
LEFT JOIN containersteprecord csd ON csd.containerid = aps.containerid AND csd.workflowstepid = aps.workflowstepid
LEFT JOIN workflowstep ws ON ws.workflowstepid = aps.workflowstepid
WHERE c.rbcontainertype IS NOT NULL AND csd.checkdate IS NOT NULL AND ws.sequence = 1 AND NVL(csd.lossqty,0) > 0
      AND csd.checkdate >= to_date('{0} 00:00:00','yyyy/mm/dd hh24:mi:ss') 
      AND csd.checkdate <= to_date('{0} 23:59:59','yyyy/mm/dd hh24:mi:ss')
      AND aps.endtime >= to_date('{1} 00:00:00','yyyy/mm/dd hh24:mi:ss') 
      AND aps.endtime <= to_date('{2} 23:59:59','yyyy/mm/dd hh24:mi:ss') AND {3} AND f.factoryname = '{4}'";

        string selectdate = jObject["selectdate"].ToString();
        string startOfSelectMonth = jObject["startOfSelectMonth"].ToString();
        string endOfSelectMonth = jObject["endOfSelectMonth"].ToString();
        string factoryname = jObject["factoryname"].ToString();
        JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
        string query = "";
        if (selectOrg.Count == 3)
        {
            //查询所选组
            query = string.Format("aps.teamid = '{0}'", selectOrg[2].ToString());
        }
        else if (selectOrg.Count == 2)
        {
            //查询所选工区下各组
            query = string.Format("aps.workcenterid = '{0}'", selectOrg[1].ToString());
        }
        else
        {
            //查询分厂下所有组
            query = string.Format("aps.specfactoryid = '{0}'", selectOrg[0].ToString());
        }

        sql = string.Format(sql, selectdate, startOfSelectMonth, endOfSelectMonth, query, factoryname);
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //c-折线图明细
    Result getCostChartDetail(JObject jObject)
    {
        string sql = @"
SELECT w.workcentername,t.teamname,cci.containername,cci.qty,cci.workflowstepname,aps.endtime,csd.toacceptdate
FROM apsplandetail aps
LEFT JOIN containercurrentinfo cci ON cci.containerid = aps.containerid --子卡
LEFT JOIN container c ON c.containerid = cci.containerid
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
LEFT JOIN workcenter w ON w.workcenterid = aps.workcenterid
LEFT JOIN team t ON t.teamid = aps.teamid
LEFT JOIN containersubcontainer sub ON sub.subcontainerid = aps.containerid
LEFT JOIN containercurrentinfo cci2 ON cci2.containerid = sub.containerid --主卡
LEFT JOIN factory f2 ON f2.factoryid = cci2.originalfactoryid
INNER JOIN apsplandetail aps2 ON aps2.containerid = sub.containerid AND aps2.workflowstepid = sub.workflowstepid AND aps2.isuse = 0 AND NVL(aps2.status,0) <> 0
INNER JOIN containershiprecordstep spd ON spd.containerid = cci2.containerid AND spd.workflowstepid = sub.workflowstepid
LEFT JOIN containershiprecord csd ON csd.containershiprecordid = spd.containershiprecordid AND csd.isuse = 0
LEFT JOIN containershiprecive csrv ON csrv.containershiprecordid = csd.containershiprecordid AND csrv.isuse = 0
LEFT JOIN containersteprecord csr ON csr.containerid = aps.containerid AND csr.workflowstepid = aps.workflowstepid AND csr.usecompleted = 1
LEFT JOIN workflowstep ws ON ws.workflowstepid = aps.workflowstepid
WHERE c.rbcontainertype IS NOT NULL AND ws.sequence = 1 AND cci.containerstatus <> 2 and cci.isInStore <> 1
      AND csd.toacceptdate IS NOT NULL AND csr.checkdate IS NULL
      AND aps.endtime >= to_date('{0} 00:00:00','yyyy/mm/dd hh24:mi:ss') 
      AND aps.endtime <= to_date('{0} 23:59:59','yyyy/mm/dd hh24:mi:ss')
      AND f.factoryname = '{1}' AND {2}";
        string selectdate = jObject["selectdate"].ToString();
        string factoryname = jObject["factoryname"].ToString();
        JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
        string query = "";
        if (selectOrg.Count == 3)
        {
            //查询所选组
            query = string.Format("aps.teamid = '{0}'", selectOrg[2].ToString());
        }
        else if (selectOrg.Count == 2)
        {
            //查询所选工区下各组
            query = string.Format("aps.workcenterid = '{0}'", selectOrg[1].ToString());
        }
        else
        {
            //查询分厂下所有组
            query = string.Format("aps.specfactoryid = '{0}'", selectOrg[0].ToString());
        }

        sql = string.Format(sql, selectdate, factoryname, query);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //d-折线图明细
    Result getDeliverChartDetail(JObject jObject)
    {
        string sql = @"
SELECT DISTINCT f2.factoryname fromfactoryname,f.factoryname tofactoryname,w.workcentername,t.teamname,cci2.containername pcontainername,
       sub.workflowstepname pworkflowstepname,
       cci.containername rbcontainername,aps.workflowstepname sworkflowstepname,cci.qty,cci.productname,cci.productdescription,
       cci.mfgordername,aps2.starttime pstarttime,aps2.endtime pendtime,aps.starttime,aps.endtime,
       csd.senddate,csd.toacceptdate,csrv.completedate,csrv.recivedate,csr.checkdate,
       CASE WHEN csd.toacceptdate IS NULL THEN '待接收'
            WHEN csd.toacceptdate IS NOT NULL AND csr.checkdate IS NULL THEN '待完成'
            WHEN csr.checkdate IS NOT NULL THEN '已完成'
       END status,
       CASE WHEN csr.checkdate IS NOT NULL AND csr.checkdate > aps.endtime THEN ROUND(csr.checkdate - aps.endtime,2)
            WHEN csr.checkdate IS NOT NULL AND csr.checkdate <= aps.endtime THEN 0
            WHEN csr.checkdate IS NULL AND SYSDATE > aps.endtime THEN ROUND(SYSDATE - aps.endtime,2)
            WHEN csr.checkdate IS NULL AND SYSDATE <= aps.endtime THEN 0
       END delaydays
FROM apsplandetail aps
LEFT JOIN containercurrentinfo cci ON cci.containerid = aps.containerid --子卡
LEFT JOIN container c ON c.containerid = cci.containerid
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
LEFT JOIN workcenter w ON w.workcenterid = aps.workcenterid
LEFT JOIN team t ON t.teamid = aps.teamid
LEFT JOIN containersubcontainer sub ON sub.subcontainerid = aps.containerid
LEFT JOIN containercurrentinfo cci2 ON cci2.containerid = sub.containerid --主卡
LEFT JOIN factory f2 ON f2.factoryid = cci2.originalfactoryid
INNER JOIN apsplandetail aps2 ON aps2.containerid = sub.containerid AND aps2.workflowstepid = sub.workflowstepid AND aps2.isuse = 0 AND NVL(aps2.status,0) <> 0
INNER JOIN containershiprecordstep spd ON spd.containerid = cci2.containerid AND spd.workflowstepid = sub.workflowstepid
LEFT JOIN containershiprecord csd ON csd.containershiprecordid = spd.containershiprecordid AND csd.isuse = 0
LEFT JOIN containershiprecive csrv ON csrv.containershiprecordid = csd.containershiprecordid AND csrv.isuse = 0
LEFT JOIN containersteprecord csr ON csr.containerid = aps.containerid AND csr.workflowstepid = aps.workflowstepid AND csr.usecompleted = 1
LEFT JOIN workflowstep ws ON ws.workflowstepid = aps.workflowstepid
WHERE c.rbcontainertype IS NOT NULL AND csd.senddate IS NOT NULL AND cci.containerstatus <> 2 AND ws.sequence = 1
      --AND csd.senddate <= aps2.starttime
      AND NOT(csr.checkdate IS NOT NULL AND csr.checkdate <= aps.endtime)
      AND f.factoryname = '{0}'
      AND aps.endtime >= to_date('{1} 00:00:00','yyyy-MM-dd hh24:mi:ss') 
      AND aps.endtime <= to_date('{1} 23:59:59','yyyy-MM-dd hh24:mi:ss')
      AND {2}";
        string factoryname = jObject["factoryname"].ToString();
        string selectdate = jObject["selectdate"].ToString();
        JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
        string query = "";
        if (selectOrg.Count == 3)
        {
            //查询所选组
            query = string.Format("aps.teamid = '{0}'", selectOrg[2].ToString());
        }
        else if (selectOrg.Count == 2)
        {
            //查询所选工区下各组
            query = string.Format("aps.workcenterid = '{0}'", selectOrg[1].ToString());
        }
        else
        {
            //查询分厂下所有组
            query = string.Format("aps.specfactoryid = '{0}'", selectOrg[0].ToString());
        }
        sql = string.Format(sql, factoryname, selectdate, query);
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //p-折线图明细
    Result getPersonChartDetail(JObject jObject)
    {
        string sql = @"
SELECT te.id,te.rbaeositemid,t.yearmonth,t.sqcdptype,t.itemname,t.itemnotes,t.orgtype,t.unit,t.threshold,t.determine,t.status,
       CASE WHEN ta.teamname IS NOT NULL THEN f.factoryname || '/' || w.workcentername || '/' || ta.teamname
            WHEN ta.teamname IS NULL AND w.workcentername IS NOT NULL THEN f.factoryname || '/' || w.workcentername
            ELSE f.factoryname
       END orginfo,
       te.description,te.improvemeasures,te.needfinishdate,te.finishdate,te.schedule,te.dealemployee,cci.containername,ws.workflowstepname,
       te.createdate,e.fullname creater,te.lastchangedate,e2.fullname updater
FROM rbaeositemdetail te
LEFT JOIN rbaeositem t ON t.id = te.rbaeositemid
LEFT JOIN factory f ON f.factoryid = t.factoryid
LEFT JOIN workcenter w ON w.workcenterid = t.workcenterid
LEFT JOIN team ta ON ta.teamid = t.teamid
LEFT JOIN employee e ON e.employeeid = te.createemployee
LEFT JOIN employee e2 ON e2.employeeid = te.lastchangeemployee
LEFT JOIN containercurrentinfo cci ON cci.containerid = te.containerid
LEFT JOIN workflowstep ws ON ws.workflowstepid = te.workflowstepid
WHERE t.status = 1 AND t.yearmonth = '{0}' AND t.orgtype = '分厂级' AND t.sqcdptype = 'P' AND t.itemname = '培训计划完成率' AND te.createdate = to_date('{1}','yyyy-mm-dd')
ORDER BY te.createdate";
        sql = string.Format(sql, jObject["yearmonth"].ToString(), jObject["selectdate"].ToString());
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }
}